import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_毕业生对所学课程的评价_多指标分析_(
    project_id: int,
    questionnaire_ids: List[int],
    group_by_field: Optional[str] = None,
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    毕业生对所学课程的评价（多指标分析） - 指标计算函数
    
    ## 指标说明
    该指标用于评估毕业生对所学课程的整体满意度，包含三个子指标：
    1. 专业课满足度（T00000403）：课程内容是否符合专业相关工作/学习需要
    2. 专业课掌握度（T00000402）：学生对专业课程内容的掌握程度
    3. 公共课帮助度（T00000405）：公共课程对专业发展的帮助程度
    
    三道题都是单选题，计算前3项占前5项之和的比例。
    最终结果为三个子指标在相同分组维度下的平均值。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        group_by_field (Optional[str]): 分组维度字段，可选值：
            - 'college': 按院系分组（默认）
            - 'spec': 按专业分组
            - 'sex': 按性别分组
            - 其他学生表字段
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入示例1：按院系分组（默认）
    ```json
    {
        "project_id": 6768,
        "questionnaire_ids": [12709, 12710]
    }
    ```
    
    ### 输入示例2：按专业分组
    ```json
    {
        "project_id": 6768,
        "questionnaire_ids": [12709, 12710],
        "group_by_field": "spec"
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "dimension_name": "计算机学院",
                "dimension_field": "college",
                "major_satisfaction": 0.8500,
                "major_mastery": 0.8200,
                "public_course_help": 0.7800,
                "overall_evaluation": 0.8167
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 毕业生对所学课程的评价（多指标分析）, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()
        
        # 设置默认分组字段
        if not group_by_field:
            group_by_field = 'college'
        
        # 验证分组字段（防止SQL注入）
        allowed_fields = ['college', 'spec', 'sex', 'education', 'grade', 'class_name']
        if group_by_field not in allowed_fields:
            logger.warning(f"分组字段 {group_by_field} 不在允许列表中，使用默认值 'college'")
            group_by_field = 'college'
        
        logger.info(f"使用分组字段: {group_by_field}")

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, dy_target_items, split_tb_paper 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        split_tb_paper = project_info['split_tb_paper']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}, split_tb_paper={split_tb_paper}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询问卷信息
        questionnaire_sql = f"""
        SELECT id, dy_target 
        FROM wt_template_customer 
        WHERE id IN ({','.join(['%s'] * len(questionnaire_ids))})
        """
        questionnaires = db.fetchall(questionnaire_sql, tuple(questionnaire_ids))
        if not questionnaires:
            raise ValueError(f"未找到问卷ID集合={questionnaire_ids}的配置信息")
        
        logger.info(f"查询到问卷信息: {questionnaires}")

        # 4. 过滤特定调研对象的问卷
        valid_questionnaire_ids = [q['id'] for q in questionnaires if q['dy_target'] == 'GRADUATE_SHORT']
        if not valid_questionnaire_ids:
            raise ValueError("未找到目标调研对象的问卷ID")
            
        logger.info(f"找到有效问卷ID: {valid_questionnaire_ids}")

        # 5. 查询问题信息
        question_codes = ['T00000403', 'T00000402', 'T00000405']
        question_ids = []
        for code in question_codes:
            question_sql = """
            SELECT id, wt_code, wt_obj 
            FROM wt_template_question_customer 
            WHERE cd_template_id = %s AND wt_code = %s AND is_del = 0
            """
            question_info = db.fetchone(question_sql, (valid_questionnaire_ids[0], code))
            if not question_info:
                raise ValueError(f"未找到问题编码={code}的问题信息")
            question_ids.append(question_info['id'])
            
        logger.info(f"找到问题ID: {question_ids}")

        # 6. 构建动态表名
        answer_table = f"re_dy_paper_answer_{split_tb_paper}"
        student_table = f"dim_client_target_baseinfo_student_{item_year}"

        # 7. 计算各维度指标（动态分组字段）
        def calculate_ratio(question_id: int) -> Dict[str, float]:
            """
            计算单个问题按指定维度分组的指标
            前3项占前5项之和的比例
            """
            sql = f"""
            SELECT
                s.{group_by_field} AS dimension_value,
                (sum(t1.c1)+sum(t1.c2)+sum(t1.c3))/ (sum(t1.c1)+sum(t1.c2)+sum(t1.c3)+sum(t1.c4)+sum(t1.c5)) as ratio
            FROM
                {answer_table} t1
                JOIN {student_table} s ON t1.target_no = s.target_no
            WHERE
                t1.cd_template_id = %s
                AND t1.wid = %s
                AND t1.ans_true = 1
                AND s.shard_tb_key = %s
                AND s.item_year = %s
                AND s.{group_by_field} IS NOT NULL
                AND s.{group_by_field} != ''
            GROUP BY
                s.{group_by_field}
            """
            results = db.fetchall(sql, (valid_questionnaire_ids[0], question_id, shard_tb_key, item_year))
            # 安全地转换 ratio，处理可能的 None 值
            return {r['dimension_value']: float(r['ratio']) if r['ratio'] is not None else 0.0 for r in results}

        # 8. 计算三个子指标
        major_satisfaction = calculate_ratio(question_ids[0])  # 专业课满足度（T00000403）
        major_mastery = calculate_ratio(question_ids[1])       # 专业课掌握度（T00000402）
        public_course_help = calculate_ratio(question_ids[2])   # 公共课帮助度（T00000405）

        logger.info(f"专业课满足度数据: {len(major_satisfaction)} 个{group_by_field}")
        logger.info(f"专业课掌握度数据: {len(major_mastery)} 个{group_by_field}")
        logger.info(f"公共课帮助度数据: {len(public_course_help)} 个{group_by_field}")

        # 9. 合并结果（获取所有维度值）
        all_dimensions = set(major_satisfaction.keys()).union(
            set(major_mastery.keys()),
            set(public_course_help.keys())
        )

        result = []
        for dimension_value in all_dimensions:
            ms = major_satisfaction.get(dimension_value, 0.0)
            mm = major_mastery.get(dimension_value, 0.0)
            pch = public_course_help.get(dimension_value, 0.0)
            
            # 计算总体评价（三个子指标的平均值）
            # 只有当三个指标都有值时才计算平均值
            valid_values = [v for v in [ms, mm, pch] if v > 0]
            if len(valid_values) == 3:
                overall = sum(valid_values) / 3
            else:
                overall = None
            
            result.append({
                "dimension_name": str(dimension_value),
                "dimension_field": group_by_field,
                "major_satisfaction": round(ms, 4) if ms > 0 else None,
                "major_mastery": round(mm, 4) if mm > 0 else None,
                "public_course_help": round(pch, 4) if pch > 0 else None,
                "overall_evaluation": round(overall, 4) if overall is not None else None
            })

        # 按维度名称排序
        result.sort(key=lambda x: x['dimension_name'])

        logger.info(f"指标 '毕业生对所学课程的评价（多指标分析）' 计算成功，共 {len(result)} 个{group_by_field}")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result
        }

    except Exception as e:
        logger.error(f"计算指标 '毕业生对所学课程的评价（多指标分析）' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 毕业生对所学课程的评价（多指标分析）",
            "code": 500,
            "error": str(e)
        }